package com.ly.common.core.utils;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.toolkit.JdbcUtils;
import com.ly.common.core.exception.ServiceException;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * sql工具类
 *
 * @author luoyun
 */
@Slf4j
@NoArgsConstructor
public class SqlUtils {

    private static final String XSS_STR = "and |exec |peformance_schema|information_schema|extractvalue|updatexml|geohash|gtid_subset|gtid_subtract|insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |;|or |+|--";
    private static String specialReportXssStr = "exec |peformance_schema|information_schema|extractvalue|updatexml|geohash|gtid_subset|gtid_subtract|insert |alter |delete |grant |update |drop |master |truncate |declare |--";
    private static String specialDictSqlXssStr = "exec |peformance_schema|information_schema|extractvalue|updatexml|geohash|gtid_subset|gtid_subtract|insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |;|+|--";
    private static List<String> FULL_MATCHING_KEYWRODS = new ArrayList();
    private static final String[] XSS_REGULAR_STR_ARRAY;
    private static final Pattern SQL_ANNOTATION;
    private static final String SQL_ANNOTATION2 = "--";
    private static final String SQL_INJECTION_KEYWORD_TIP = "请注意，存在SQL注入关键词---> {}";
    private static final String SQL_INJECTION_TIP = "请注意，值可能存在SQL注入风险!--->";
    private static final String SQL_INJECTION_TIP_VARIABLE = "请注意，值可能存在SQL注入风险!---> {}";
    private static Pattern tableNamePattern;
    static final Pattern fieldPattern;
    /**
     * 定义常用的 sql关键字
     */
    public static String SQL_REGEX = "select |insert |delete |update |drop |count |exec |chr |mid |master |truncate |char |and |declare ";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    private static DbType dbTypeEnum = null;

    static {
        FULL_MATCHING_KEYWRODS.add(";");
        FULL_MATCHING_KEYWRODS.add("+");
        FULL_MATCHING_KEYWRODS.add("--");
        XSS_REGULAR_STR_ARRAY = new String[]{"chr\\s*\\(", "mid\\s*\\(", " char\\s*\\(", "sleep\\s*\\(", "user\\s*\\(", "show\\s+tables", "user[\\s]*\\([\\s]*\\)", "show\\s+databases", "sleep\\(\\d*\\)", "sleep\\(.*\\)"};
        SQL_ANNOTATION = Pattern.compile("/\\*[\\s\\S]*\\*/");
        //64位以字母开头，剩下63位字母、数字、下划线 _ 或美元符号 $
        tableNamePattern = Pattern.compile("^[a-zA-Z][a-zA-Z0-9_\\$]{0,63}$");
        fieldPattern = Pattern.compile("^[a-zA-Z0-9_]+$");
    }

    /**
     * 获取数据库类型
     * @return
     */
    public static DbType getDatabaseTypeEnum() {
        if (ObjectUtil.isNotEmpty(dbTypeEnum)) {
            return dbTypeEnum;
        } else {
            try {
                DataSource dataSource = (DataSource)SpringUtils.getBean(DataSource.class);
                dbTypeEnum = JdbcUtils.getDbType(dataSource.getConnection().getMetaData().getURL());
                return dbTypeEnum;
            } catch (SQLException var1) {
                log.warn(var1.getMessage(), var1);
                return null;
            }
        }
    }

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value) {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value)) {
            throw new ServiceException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value) {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     */
    public static void filterKeyword(String value) {
        if (StrUtil.isEmpty(value)) {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (int i = 0; i < sqlKeywords.length; i++) {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeywords[i]) > -1) {
                throw new ServiceException("参数存在SQL注入风险");
            }
        }
    }

    public static void filterContentMulti(String... values) {
        filterContent((String[])values, (String)null);
    }

    public static void filterContent(String[] values, String customXssString) {
        String[] var2 = values;
        int var3 = values.length;

        for(int var4 = 0; var4 < var3; ++var4) {
            String val = var2[var4];
            if (StrUtil.isEmpty(val)) {
                return;
            }

            filterContent(val, customXssString);
        }

    }

    public static void filterContent(String value, String customXssString) {
        if (value != null && !"".equals(value)) {
            checkSqlAnnotation(value);
            value = value.toLowerCase().trim();
            String[] xssArr = "and |exec |peformance_schema|information_schema|extractvalue|updatexml|geohash|gtid_subset|gtid_subtract|insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |;|or |+|--".split("\\|");

            for(int i = 0; i < xssArr.length; ++i) {
                if (value.indexOf(xssArr[i]) > -1) {
                    log.error("请注意，存在SQL注入关键词---> {}", xssArr[i]);
                    log.error("请注意，值可能存在SQL注入风险!---> {}", value);
                    throw new ServiceException("请注意，值可能存在SQL注入风险!--->" + value);
                }
            }

            int i;
            String[] xssArr2;
            if (customXssString != null) {
                xssArr2 = customXssString.split("\\|");

                for(i = 0; i < xssArr2.length; ++i) {
                    if (value.indexOf(xssArr2[i]) > -1) {
                        log.error("请注意，存在SQL注入关键词---> {}", xssArr2[i]);
                        log.error("请注意，值可能存在SQL注入风险!---> {}", value);
                        throw new ServiceException("请注意，值可能存在SQL注入风险!--->" + value);
                    }
                }
            }

            xssArr2 = XSS_REGULAR_STR_ARRAY;
            i = xssArr2.length;

            for(int var5 = 0; var5 < i; ++var5) {
                String regularOriginal = xssArr2[var5];
                String regular = ".*" + regularOriginal + ".*";
                if (Pattern.matches(regular, value)) {
                    log.error("请注意，存在SQL注入关键词---> {}", regularOriginal);
                    log.error("请注意，值可能存在SQL注入风险!---> {}", value);
                    throw new ServiceException("请注意，值可能存在SQL注入风险!--->" + value);
                }
            }

        }
    }

    private static boolean isExistSqlInjectKeyword(String sql, String keyword) {
        if (sql.startsWith(keyword.trim())) {
            return true;
        } else if (sql.contains(keyword)) {
            String matchingText = " " + keyword;
            if (FULL_MATCHING_KEYWRODS.contains(keyword)) {
                matchingText = keyword;
            }

            if (sql.contains(matchingText)) {
                return true;
            } else {
                String regularStr = "\\s+\\S+" + keyword;
                List<String> resultFindAll = (List) ReUtil.findAll(regularStr, sql, 0, new ArrayList());
                Iterator var5 = resultFindAll.iterator();

                String res;
                do {
                    if (!var5.hasNext()) {
                        return false;
                    }

                    res = (String)var5.next();
                    log.info("isExistSqlInjectKeyword —- 匹配到的SQL注入关键词：{}", res);
                } while(!res.contains("%") && !res.contains("+") && !res.contains("#") && !res.contains("/") && !res.contains(")"));

                return true;
            }
        } else {
            return false;
        }
    }

    public static void specialFilterContentForDictSql(String value) {
        String[] xssArr = specialDictSqlXssStr.split("\\|");
        if (value != null && !"".equals(value)) {
            checkSqlAnnotation(value);
            value = value.toLowerCase().trim();

            for(int i = 0; i < xssArr.length; ++i) {
                if (isExistSqlInjectKeyword(value, xssArr[i])) {
                    log.error("请注意，存在SQL注入关键词---> {}", xssArr[i]);
                    log.error("请注意，值可能存在SQL注入风险!---> {}", value);
                    throw new ServiceException("请注意，值可能存在SQL注入风险!--->" + value);
                }
            }

            String[] var7 = XSS_REGULAR_STR_ARRAY;
            int var3 = var7.length;

            for(int var4 = 0; var4 < var3; ++var4) {
                String regularOriginal = var7[var4];
                String regular = ".*" + regularOriginal + ".*";
                if (Pattern.matches(regular, value)) {
                    log.error("请注意，存在SQL注入关键词---> {}", regularOriginal);
                    log.error("请注意，值可能存在SQL注入风险!---> {}", value);
                    throw new ServiceException("请注意，值可能存在SQL注入风险!--->" + value);
                }
            }

        }
    }

    public static void specialFilterContentForOnlineReport(String value) {
        String[] xssArr = specialReportXssStr.split("\\|");
        if (value != null && !"".equals(value)) {
            checkSqlAnnotation(value);
            value = value.toLowerCase().trim();

            for(int i = 0; i < xssArr.length; ++i) {
                if (isExistSqlInjectKeyword(value, xssArr[i])) {
                    log.error("请注意，存在SQL注入关键词---> {}", xssArr[i]);
                    log.error("请注意，值可能存在SQL注入风险!---> {}", value);
                    throw new ServiceException("请注意，值可能存在SQL注入风险!--->" + value);
                }
            }

            String[] var7 = XSS_REGULAR_STR_ARRAY;
            int var3 = var7.length;

            for(int var4 = 0; var4 < var3; ++var4) {
                String regularOriginal = var7[var4];
                String regular = ".*" + regularOriginal + ".*";
                if (Pattern.matches(regular, value)) {
                    log.error("请注意，存在SQL注入关键词---> {}", regularOriginal);
                    log.error("请注意，值可能存在SQL注入风险!---> {}", value);
                    throw new ServiceException("请注意，值可能存在SQL注入风险!--->" + value);
                }
            }

        }
    }

    public static void checkSqlAnnotation(String str) {
        if (str.contains("--")) {
            String error = "请注意，SQL中不允许含注释，有安全风险！";
            log.error(error);
            throw new RuntimeException(error);
        } else {
            Matcher matcher = SQL_ANNOTATION.matcher(str);
            if (matcher.find()) {
                String error = "请注意，值可能存在SQL注入风险---> \\*.*\\";
                log.error(error);
                throw new ServiceException(error);
            }
        }
    }

    public static String getSqlInjectTableName(String table) {
        if (StrUtil.isEmpty(table)) {
            return table;
        } else {
            int index = table.toLowerCase().indexOf(" where ");
            if (index != -1) {
                table = table.substring(0, index);
                log.info("截掉where之后的新表名：" + table);
            }

            table = table.trim();
            boolean isValidTableName = tableNamePattern.matcher(table).matches();
            if (!isValidTableName) {
                String errorMsg = "表名不合法，存在SQL注入风险!--->" + table;
                log.error(errorMsg);
                throw new ServiceException(errorMsg);
            } else {
                filterContentMulti(table);
                return table;
            }
        }
    }

    public static String getSqlInjectField(String field) {
        if (StrUtil.isEmpty(field)) {
            return field;
        } else {
            field = field.trim();
            if (field.contains(",")) {
                return getSqlInjectField(field.split(","));
            } else {
                boolean isValidField = fieldPattern.matcher(field).matches();
                if (!isValidField) {
                    String errorMsg = "字段不合法，存在SQL注入风险!--->" + field;
                    log.error(errorMsg);
                    throw new ServiceException(errorMsg);
                } else {
                    filterContentMulti(field);
                    return field;
                }
            }
        }
    }

    public static String getSqlInjectField(String... fields) {
        String[] var1 = fields;
        int var2 = fields.length;

        for(int var3 = 0; var3 < var2; ++var3) {
            String s = var1[var3];
            getSqlInjectField(s);
        }

        return String.join(",", fields);
    }

    public static String getSqlInjectSortField(String sortField) {
        String field = getSqlInjectField(StrUtil.toUnderlineCase(sortField));
        return field;
    }

    public static List getSqlInjectSortFields(String... sortFields) {
        List list = new ArrayList();
        String[] var2 = sortFields;
        int var3 = sortFields.length;

        for(int var4 = 0; var4 < var3; ++var4) {
            String sortField = var2[var4];
            list.add(getSqlInjectSortField(sortField));
        }

        return list;
    }

    public static String getSqlInjectOrderType(String orderType) {
        if (orderType == null) {
            return null;
        } else {
            orderType = orderType.trim();
            return "ASC".equalsIgnoreCase(orderType) ? "ASC" : "DESC";
        }
    }
}